package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.util.Constant;

public class IdaLogDAO 
{
	private static String db = (new Constant()).getPara("DATABASE");
	
	public static long logLogin(String userId,String inIp){
		if (!Constant.getStPara("LOG_ENABLED").equalsIgnoreCase("YES"))
			return -1;
		long logId = System.currentTimeMillis();
		Dao dao = new DaoImp();
		String sql = "INSERT INTO " + db + ".IDA_LOG_LOGIN(Login_Log_Id,User_Id,In_Time,Out_Time,In_Ip,Remark) " +
				"VALUES(" + logId + ",'" + userId + "'," +
				"CAST(date AS DATE FORMAT 'YYYY-MM-DDB') || time,,'" + inIp + "','')";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return logId;
	}
	
	public static long logAccs(long loginLogId,int accsItemId,String accsItemTypeId,String remark){
		if (!Constant.getStPara("LOG_ENABLED").equalsIgnoreCase("YES"))
			return -1;
		long logId = System.currentTimeMillis();
		Dao dao = new DaoImp();
		String sql = "INSERT INTO " + db + ".IDA_LOG_ACCS(Accs_Log_Id,Login_Log_Id,Accs_Item_Id,Accs_Item_Type_Id,Accs_Time,Remark) " +
				"VALUES(" + logId + "," + loginLogId + "," + accsItemId + ",'" + accsItemTypeId + "'," +
				"CAST(date AS DATE FORMAT 'YYYY-MM-DDB') || time,'" + remark + "')";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return logId;
	}
	
	public static void logExecute(long accsLogId,String execTypeId,String execSql,int recordCount,String startTime,String endTime,String remark){
		if (!Constant.getStPara("LOG_ENABLED").equalsIgnoreCase("YES"))
			return;
		StringBuffer sb = new StringBuffer();
		if (execSql.trim().indexOf("'") != -1){
			char[] c = execSql.toCharArray();
			for(int i = 0; i < c.length; i++){
				sb.append(c[i]);
				if(c[i] == '\'')
					sb.append(c[i]);
			}
		}
		else{
			sb.append(execSql);
		}
		Dao dao = new DaoImp();
		String sql = "INSERT INTO " + db + ".IDA_LOG_EXECUTE(Accs_Log_Id,Exec_Type_Id,Exec_Sql,Record_Count,Start_Time,End_Time,Remark) " +
				"VALUES(" + accsLogId + ",'" + execTypeId + "','" + sb.toString() + "'," + recordCount + "," +
				"'" + startTime + "','" + endTime + "','" + remark + "')";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public static List queryLoginDaily(String startDate,String endDate,String userId){
		List result = new ArrayList();
		Dao dao = new DaoImp();
		String sql = "SELECT "
					+ "	 CAST(CAST(T1.In_Time AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) (TITLE '访问日期') "
					+ "	 ,COUNT(DISTINCT T1.User_Id) (TITLE '访问人数') "
					+ "	 ,COUNT(DISTINCT T1.Login_Log_Id) (TITLE '系统登录次数') "
					+ "	 ,ZEROIFNULL(COUNT(DISTINCT T2.Accs_Log_Id)) (TITLE '报表访问次数') "
					+ "FROM	" + db + ".IDA_LOG_LOGIN T1 "
					+ "LEFT JOIN " + db + ".IDA_LOG_ACCS T2 "
					+ "ON		T1.Login_Log_Id = T2.Login_Log_Id "
					+ "WHERE 	CAST(CAST(T1.In_Time AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) BETWEEN '" + startDate + "' AND '" + endDate + "' " +
							(userId.equals("") ? " " : " AND T1.User_Id = '" + userId + "'")
					+ "GROUP BY 1 "
					+ "ORDER BY 1 DESC";
		try{
			ResultSet rs = dao.select(sql);
			int colCnt = rs.getMetaData().getColumnCount();
			Object[] titleRow = new Object[colCnt];
			for (int i = 0; i < colCnt; i++)
				titleRow[i] = rs.getMetaData().getColumnLabel(i + 1);
			result.add(titleRow);
			while (rs.next()){
				Object[] row = new Object[colCnt];
				for (int i = 0; i < colCnt; i++)
					row[i] = rs.getObject(i + 1);
				result.add(row);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return result;
	}
	
	public static List queryLoginHour(String startDate,String endDate,String userId){
		List result = new ArrayList();
		Dao dao = new DaoImp();
		String sql = "SELECT "
					+ "	 CAST(TRIM(EXTRACT(HOUR FROM In_Time)) AS INTEGER) || ' 时'	 (TITLE '时段') "
					+ "	 ,COUNT(DISTINCT T1.User_Id) (TITLE '访问人数') "
					+ "	 ,COUNT(DISTINCT T1.Login_Log_Id) (TITLE '系统登录次数') "
					+ "	 ,ZEROIFNULL(COUNT(DISTINCT T2.Accs_Log_Id)) (TITLE '报表访问次数') "
					+ "FROM	" + db + ".IDA_LOG_LOGIN T1 "
					+ "LEFT JOIN " + db + ".IDA_LOG_ACCS T2 "
					+ "ON		T1.Login_Log_Id = T2.Login_Log_Id "
					+ "WHERE 	CAST(CAST(T1.In_Time AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) BETWEEN '" + startDate + "' AND '" + endDate + "' " +
							(userId.equals("") ? " " : " AND T1.User_Id = '" + userId + "'")
					+ "GROUP BY 1 "
					+ "ORDER BY 1 ";
		try{
			ResultSet rs = dao.select(sql);
			int colCnt = rs.getMetaData().getColumnCount();
			Object[] titleRow = new Object[colCnt];
			for (int i = 0; i < colCnt; i++)
				titleRow[i] = rs.getMetaData().getColumnLabel(i + 1);
			result.add(titleRow);
			while (rs.next()){
				Object[] row = new Object[colCnt];
				for (int i = 0; i < colCnt; i++)
					row[i] = rs.getObject(i + 1);
				result.add(row);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return result;
	}
	
	public static List queryAccsDaily(String startDate,String endDate,String userId){
		List result = new ArrayList();
		Dao dao = new DaoImp();
		String sql = "SELECT                                                                                                         "
			+ "	(CASE                                                                                                        "
			+ "		WHEN T1.Accs_Item_Type_Id = '11' THEN T3.Template_Name                                                     "
			+ "		WHEN T1.Accs_Item_Type_Id = '12' THEN T4.Instance_Name                                                     "
			+ "		WHEN T1.Accs_Item_Type_Id = '13' THEN '自定义取数'                                                         "
			+ "	END)	(TITLE '报表名称')                                                                                     "
			+ "	,COUNT(DISTINCT T2.User_Id)	(TITLE '访问人数')                                                               "
			+ "	,COUNT(*)	(TITLE '访问次数')                                                                                 "
			+ "FROM	" + db + ".IDA_LOG_ACCS T1                                                                         "
			+ "JOIN	" + db + ".IDA_LOG_LOGIN T2                                                                        "
			+ "ON		T1.Login_Log_Id = T2.Login_Log_Id                                                                        "
			+ "LEFT JOIN	" + db + ".IDA_TEMPLATE T3                                                                   "
			+ "ON		T1.Accs_Item_Id = T3.Template_Id                                                                         "
			+ "LEFT JOIN	" + db + ".IDA_TEMPLATE_INSTANCE T4                                                          "
			+ "ON		T1.Accs_Item_Id = T4.Instance_Id                                                                         "
			+ "WHERE 	CAST(CAST(T1.Accs_Time AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) BETWEEN '" + startDate + "' AND '" + endDate + "' " +
					(userId.equals("") ? " " : " AND T2.User_Id = '" + userId + "'")
			+ "GROUP BY 1                                                                                                    "
			+ "ORDER BY 2 DESC";
		try{
			ResultSet rs = dao.select(sql);
			int colCnt = rs.getMetaData().getColumnCount();
			Object[] titleRow = new Object[colCnt];
			for (int i = 0; i < colCnt; i++)
				titleRow[i] = rs.getMetaData().getColumnLabel(i + 1);
			result.add(titleRow);
			while (rs.next()){
				Object[] row = new Object[colCnt];
				for (int i = 0; i < colCnt; i++)
					row[i] = rs.getObject(i + 1);
				result.add(row);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return result;
	}
	
	public static List queryLogUser(String startDate,String endDate,String userId){
		List result = new ArrayList();
		Dao dao = new DaoImp();
		String sql = "SELECT "
					+ " T3.Nick_Name || ' （' || T3.User_Name || '）' (TITLE '用户名称') "
					+ "	,COUNT(DISTINCT T1.Login_Log_Id) (TITLE '登录系统次数') "
					+ "	,ZEROIFNULL(COUNT(DISTINCT T2.Accs_Log_Id)) (TITLE '访问报表次数') "
					+ "	,ZEROIFNULL(COUNT(DISTINCT T2.Accs_Item_Id)) (TITLE '访问不同报表数') "
					+ "FROM	" + db + ".IDA_LOG_LOGIN T1 "
					+ "LEFT JOIN	" + db + ".IDA_LOG_ACCS T2 "
					+ "ON		T1.Login_Log_Id = T2.Login_Log_Id "
					+ "JOIN	" + db + ".SYS_USER T3 "
					+ "ON		T1.User_Id = T3.User_Id "
					+ "WHERE 	CAST(CAST(T1.In_Time AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) BETWEEN '" + startDate + "' AND '" + endDate + "' " +
							(userId.equals("") ? " " : " AND T1.User_Id = '" + userId + "'")
					+ "GROUP BY 1 "
					+ "ORDER BY 1";
		try{
			ResultSet rs = dao.select(sql);
			int colCnt = rs.getMetaData().getColumnCount();
			Object[] titleRow = new Object[colCnt];
			for (int i = 0; i < colCnt; i++)
				titleRow[i] = rs.getMetaData().getColumnLabel(i + 1);
			result.add(titleRow);
			while (rs.next()){
				Object[] row = new Object[colCnt];
				for (int i = 0; i < colCnt; i++)
					row[i] = rs.getObject(i + 1);
				result.add(row);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return result;
	}
	
	public static List queryLogDetail(String startDate,String endDate,String userId){
		List result = new ArrayList();
		Dao dao = new DaoImp();
		String sql = "SELECT                                                                                                         "
					+ "	T3.Nick_Name || ' [' || T3.Nick_Name || ']' (TITLE '用户名称')                                             "
					+ "	,T1.In_Ip (TITLE '用户IP')                                                                                 "
					+ "	,T1.In_Time (TITLE '登录时间')                                                                             "
					+ "	,(CASE                                                                                                     "
					+ "		WHEN T2.Accs_Log_Id IS NULL THEN T1.In_Time                                                              "
					+ "		ELSE T2.Accs_Time                                                                                        "
					+ "	 END) (TITLE '访问时间')                                                                                   "
					+ "	,(CASE                                                                                                     "
					+ "		WHEN T2.Accs_Log_Id IS NULL THEN '首页'                                                                  "
					+ "		WHEN T2.Accs_Item_Type_Id = '11' THEN T4.Template_Name                                                   "
					+ "		WHEN T2.Accs_Item_Type_Id = '12' THEN T5.Instance_Name                                                   "
					+ "		WHEN T2.Accs_Item_Type_Id = '13' THEN '自定义取数'                                                       "
					+ "	 END)	(TITLE '报表名称')                                                                                   "
					+ "FROM	" + db + ".IDA_LOG_LOGIN T1                                                                      "
					+ "LEFT JOIN	" + db + ".IDA_LOG_ACCS T2                                                                 "
					+ "ON		T1.Login_Log_Id = T2.Login_Log_Id                                                                      "
					+ "JOIN	" + db + ".SYS_USER T3                                                                           "
					+ "ON		T1.User_Id = T3.User_Id                                                                                "
					+ "LEFT JOIN	" + db + ".IDA_TEMPLATE T4                                                                 "
					+ "ON		T2.Accs_Item_Id = T4.Template_Id                                                                       "
					+ "LEFT JOIN	" + db + ".IDA_TEMPLATE_INSTANCE T5                                                        "
					+ "ON		T2.Accs_Item_Id = T5.Instance_Id                                                                       "
					+ "WHERE 	CAST(CAST(T1.In_Time AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) BETWEEN '" + startDate + "' AND '" + endDate + "' " +
							(userId.equals("") ? " " : " AND T1.User_Id = '" + userId + "'")
					+ "ORDER BY 3 DESC,4 DESC ";   
		try{
			ResultSet rs = dao.select(sql);
			int colCnt = rs.getMetaData().getColumnCount();
			Object[] titleRow = new Object[colCnt];
			for (int i = 0; i < colCnt; i++)
				titleRow[i] = rs.getMetaData().getColumnLabel(i + 1);
			result.add(titleRow);
			while (rs.next()){
				Object[] row = new Object[colCnt];
				for (int i = 0; i < colCnt; i++)
					row[i] = rs.getObject(i + 1);
				result.add(row);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return result;
	}
}
